package com.pcjavanet.collect.mes.dao.impl.jdbc.postgresql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.pcjavanet.collect.mes.dao.AbstractDao;
import com.pcjavanet.collect.mes.dao.OriginalMesDataDao;
import com.pcjavanet.collect.mes.to.ContinuousOriginalData;
import com.pcjavanet.collect.mes.to.DiscreteOriginalData;
import com.pcjavanet.collect.mes.to.ParetoData;
import com.pcjavanet.collect.mes.util.CommonServerUtil;
import com.pcjavanet.collect.mes.util.SystemConstant;
import com.pcjavanet.domain.Attribute;
import com.pcjavanet.domain.SPCObject;
import com.pcjavanet.monitor.Monitor;

/**
 * Provider the same as function' OriginalDataDaoImpl.java. But it fetch data from MDS database' mesdb[schema] 
 * @author Jack Mo
 *
 */
public class OriginalMesDataDaoImpl extends AbstractDao implements OriginalMesDataDao {
	
	protected static Logger logger = Logger.getLogger(OriginalMesDataDaoImpl.class);
	
	public List<DiscreteOriginalData> getDiscreteOriginalData( SPCObject monitor , int lastGroupTime ) throws DataAccessException {
		final List<DiscreteOriginalData> discreteOriginalDataList = new ArrayList<DiscreteOriginalData>();
 
			StringBuffer buf = new StringBuffer();
			buf.append(" SELECT  action AS passed, INT4( EXTRACT( EPOCH FROM (SELECT date_trunc('second',ts)))) as \"a_DateTime\",location_key,part_number,serial_number,"
					+ " to_char(ts,'YYYY-MM-DD HH24:MI:SS') as dateTimeStr ");
			buf.append(" FROM   spc.v_serial_activity_defect  ");//spc.v_serial_activity_defect
			buf.append(" WHERE (action=11 or action=12) ");
			if (monitor.getMonitorType().equals( SystemConstant.MONITOR_TYPE_PROCESS ) ) {
				buf.append(" AND process_name ='" + monitor.getShortName() + "' ");
			} else {
				if (monitor.getMonitorType().equals( SystemConstant.MONITOR_TYPE_WORKSTATION ) ) {
					buf.append(" AND workstation ='" + monitor.getShortName() + "' ");
				} else {
//					String locationFamily = monitor.getExtf1();
//					locationFamily = locationFamily.replaceAll(",", "','");
//					locationFamily = "'" + locationFamily + "'";
//					buf.append(" AND workstation in (" + locationFamily + ") ");
				}
			}
//			if (CommonServerUtil.judgeFamily(monitor.getPartNumber(), monitor.getPartFamilyName( ) ) ) {
//				buf.append(" AND family_id='" + monitor.getPartFamilyName()	+ "' ");
//			} else {
//				buf.append(" AND part_number='" + monitor.getPartNumber() + "' ");
//			}
			buf.append(" AND anet_userid='" + monitor.getNetUserId() + "' ");
			buf.append(" AND ts >to_timestamp(" + lastGroupTime +")");
			buf.append(" ORDER BY  ts  ASC LIMIT 15000 ");
 
			long startTime = System.currentTimeMillis();
			try {
				jdbcTemplate.query(buf.toString(), new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						DiscreteOriginalData discreteOriginalData = new DiscreteOriginalData();
						String lastDate = rs.getString("dateTimeStr");
						discreteOriginalData.setYieldTime(lastDate);
						if (rs.getInt("passed") == 11) {
							discreteOriginalData.setPass(1);
							discreteOriginalData.setFail(0);
						} else {
							discreteOriginalData.setPass(0);
							discreteOriginalData.setFail(1);
						}
						discreteOriginalData.setAction(rs.getInt("passed"));
						discreteOriginalData.setADateTime(rs.getLong("a_DateTime"));//a_DateTime
						discreteOriginalData.setLocationKey(rs.getInt("location_key"));
						discreteOriginalData.setPartNumber(rs.getString("part_number"));
						discreteOriginalData.setSerialNumber(rs.getString("serial_number"));
						discreteOriginalData.setTotal(1);
						discreteOriginalDataList.add(discreteOriginalData);
					}
				});
 
			} catch (Exception e) {
 
				logger.error(e);
			}
	 
		return discreteOriginalDataList;
	}

	public List<ContinuousOriginalData> getContinuousOriginalData( SPCObject monitor ) throws DataAccessException {
		final List<ContinuousOriginalData> continuousOriginalDataList = new ArrayList<ContinuousOriginalData>();
//		String attribute = monitor.getAttributeName();
//		attribute = attribute.replaceAll(",", "','");
		List<Attribute> attributes=monitor.getAttributes();
		StringBuffer bufAtt = new StringBuffer();
		for( int i=0; i<attributes.size();i++) {
			bufAtt.append("'").append(attributes.get(i).getAttributeName()).append("'");
			if ( i !=( attributes.size() -1 ) ) {
				bufAtt.append(",");
			}  
		}
		String  attribute = bufAtt.toString() ;
		attribute = "'" + attribute + "'";
		if (!attribute.equals("''")) {
			long lastGroupTime = monitor.getLastUpdateTime();
			StringBuffer buf = new StringBuffer();
			buf.append(" select serial_number, cparam1,  cparam2,  INT4( EXTRACT( EPOCH FROM (SELECT date_trunc('second',ts)))) as \"a_DateTime\" ,part_number ");
			buf.append(" FROM spc.v_serial_activity_defect  where ");
			if (monitor.getMonitorType().equals( SystemConstant.MONITOR_TYPE_PROCESS)) {
				buf.append(" process_name ='" + monitor.getShortName()	+ "' ");
			} else {
				if (monitor.getMonitorType().equals( SystemConstant.MONITOR_TYPE_WORKSTATION ) ) {
					buf.append(" workstation ='" + monitor.getShortName() + "' ");
				} else {
//					String locationFamily = monitor.getExtf1();
//					locationFamily = locationFamily.replaceAll(",", "','");
//					locationFamily = "'" + locationFamily + "'";
//					buf.append(" workstation in (" + locationFamily + ") ");
				}
			}
//			if (CommonServerUtil.judgeFamily(monitor.getPartName(),monitor.getPartFamilyName())) {
//				buf.append(" AND family_id='" + monitor.getPartFamilyName()	+ "' ");
//			} else {
//				buf.append(" AND part_number='" + monitor.getPartName() + "' ");
//			}
			buf.append(" AND cparam1 in ( " + attribute + " )");
			buf.append(" AND anet_userid='" + monitor.getNetUserId() + "' ");
//			buf.append(" AND \"a_DateTime\">" + lastGroupTime);
			buf.append(" AND ts >to_timestamp(" + lastGroupTime +")");
			buf.append(" ORDER BY  ts ASC limit 15000 ");
 
			long startTime = System.currentTimeMillis();
			try {
				jdbcTemplate.query(buf.toString(), new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ContinuousOriginalData continuousOriginalData = new ContinuousOriginalData();
						continuousOriginalData.setSerialNumber(rs.getString("serial_number"));
						continuousOriginalData.setParam1(rs.getString("cparam1"));
						continuousOriginalData.setParam2(rs.getString("cparam2"));
//						continuousOriginalData.setYieldDate(rs.getString(4));
//						continuousOriginalData.setYieldTime(rs.getInt(5));
						continuousOriginalData.setADateTime(rs.getLong("a_DateTime"));
						continuousOriginalData.setPartNumber(rs.getString("part_number"));
						continuousOriginalDataList.add(continuousOriginalData);
					}
				});
 
			} catch (Exception e) {
 
				logger.error(e);
			}
		}
		return continuousOriginalDataList;
	}

	public List<DiscreteOriginalData> getDefectDiscreteOriginalData( SPCObject monitor, int lastGroupTime, long lastDataTime) throws DataAccessException {

		final List<DiscreteOriginalData> discreteOriginalDataList = new ArrayList<DiscreteOriginalData>();
		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT serial_number,  INT4( EXTRACT( EPOCH FROM (SELECT date_trunc('second',ts)))) as \"a_DateTime\" ,defectseq,defectcode,defectqty ");
		buf.append(" FROM spc.v_serial_activity_defect  ");
		buf.append(" WHERE defectcode NOT LIKE 'B%'   AND action=41 ");
		if (monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_PROCESS)) {
			buf.append(" AND process_name ='" + monitor.getShortName() + "' ");
		} else {
			if (monitor.getMonitorType().equals( SystemConstant.MONITOR_TYPE_WORKSTATION)) {
				buf.append(" AND workstation ='" + monitor.getShortName() + "' ");
			} else {
//				String locationFamily = monitor.getExtf1();
//				locationFamily = locationFamily.replaceAll(",", "','");
//				locationFamily = "'" + locationFamily + "'";
//				buf.append(" AND workstation IN (" + locationFamily + ") ");
			}
		}

//		if (CommonServerUtil.judgeFamily(monitor.getPartNumber(), monitor.getPartFamilyName())) {
//			buf.append(" AND family_id='" + monitor.getPartFamilyName() + "' ");
//		} else {
//			buf.append(" AND part_number='" + monitor.getPartNumber() + "' ");
//		}
		buf.append(" AND anet_userid='" + monitor.getNetUserId() + "' ");
//		buf.append(" AND \"a_DateTime\">" + lastGroupTime);
//		buf.append(" AND \"a_DateTime\"<=" + lastDataTime);
		buf.append(" AND ts >to_timestamp(" + lastGroupTime +")");
		buf.append(" AND ts <=to_timestamp(" + lastDataTime +")");
		buf.append(" ORDER BY ts  ASC ");
 
		
		long startTime = System.currentTimeMillis();
		try {
			jdbcTemplate.query(buf.toString(), new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					DiscreteOriginalData discreteOriginalData = new DiscreteOriginalData();
					discreteOriginalData.setSerialNumber(rs.getString("serial_number"));
					discreteOriginalData.setADateTime(rs.getLong("a_DateTime"));
					discreteOriginalData.setDefects(rs.getInt("defectqty"));//getInt(5)
					discreteOriginalDataList.add(discreteOriginalData);
				}
			});
 
		} catch (Exception e) {
 
			logger.error(e);
		}
		return discreteOriginalDataList;
	}

	public List<ParetoData> getParetoOriginalData(final SPCObject monitor, int lastGroupTime) {
		String workstation = "";
		if (monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_PROCESS)) {
			workstation = "  process_name ='" + monitor.getShortName() + "' ";
		} else {
			if (monitor.getMonitorType().equals( SystemConstant.MONITOR_TYPE_WORKSTATION)) {
				workstation = " workstation = '" + monitor.getShortName() + "' ";
			} else {
//				String locationFamily = monitor.getExtf1();
//				locationFamily = locationFamily.replaceAll(",", "','");
//				locationFamily = "'" + locationFamily + "'";
//				workstation = " workstation IN (" + locationFamily + ") ";
			}
		}
		workstation = workstation + " AND anet_userid = '" + monitor.getNetUserId() + "'  ";
		String partNumberType = "";
//		if (CommonServerUtil.judgeFamily(monitor.getPartNumber(), monitor.getPartFamilyName())) {
//			partNumberType = "  family_id = '" + monitor.getPartFamilyName() + "' ";
//		} else {
//			partNumberType = "  part_number='" + monitor.getPartNumber() + "' ";
//		}
		partNumberType = partNumberType + " AND anet_userid ='"	+ monitor.getNetUserId() + "' ";
		final List<ParetoData> datas = new LinkedList<ParetoData>();
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("  SELECT  defectqty,serial_number as s_serial_number, defectcode as sf_defectcode ,location_key as a_location_key , INT4( EXTRACT( EPOCH FROM (SELECT date_trunc('second',ts))))  as a_a_datetime	");
		sqlBuf.append("  FROM spc.v_serial_activity_defect ");
		sqlBuf.append(	"  WHERE   defectcode NOT LIKE 'B%' AND action=41  AND  ts >to_timestamp(").append(lastGroupTime).append(")");
		sqlBuf.append("  AND ").append(workstation);
		sqlBuf.append("   AND").append(partNumberType);
		sqlBuf.append("  ORDER BY  ts ASC");
 
		long startTime = System.currentTimeMillis();
		try {
			jdbcTemplate.query(sqlBuf.toString(), new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					ParetoData bean = new ParetoData();
					bean.setDefectqty(rs.getInt("defectqty"));
					bean.setaDateTime(rs.getInt("a_a_datetime"));
					bean.setDefectcode(rs.getString("sf_defectcode"));
					bean.setLocationKey(rs.getString("a_location_key"));
					bean.setSerialNumber(rs.getString("s_serial_number"));
					bean.setMonitorkey(monitor.getObjectKey());
					bean.setDescription("");
					datas.add(bean);
				}
			} );
			
 
		} catch (Exception e) {
 
			logger.error(e);
		}

		if (datas.size() > 0) {
			StringBuffer defcodes = new StringBuffer();
			defcodes.append(" 1=1 ");

			// for filter defectode first put map,use map key is unique
			Map<String, String> defectcodesMap = new HashMap<String, String>();
			for (int i = 0; i < datas.size(); i++) {
				defectcodesMap.put(datas.get(i).getDefectcode(), datas.get(i).getDefectcode());
			}
			List<String> defectcodeLs = new ArrayList<String>();
			if (defectcodesMap.size() > 0) {
				java.util.Iterator<String> keys = defectcodesMap.keySet().iterator();
				while (keys.hasNext()) {
					defectcodeLs.add(keys.next());
				}
			}

			for (int i = 0; i < defectcodeLs.size(); i++) {
				if (i == 0)
					defcodes.append("AND  defect_code IN (");
				if (i == (defectcodeLs.size() - 1)) {
					defcodes.append("'").append(defectcodeLs.get(i)).append("'");
					defcodes.append(" )");
				} else {
					defcodes.append("'").append(defectcodeLs.get(i)).append("' , ");
				}
			}

			StringBuffer defcodesSql = new StringBuffer();
			defcodesSql.append("SELECT DISTINCT defect_code as defectcode ,description FROM spc.vr_spc_defcode   WHERE  ").append(defcodes.toString()).append("  ");
//			defcodesSql.append("  AND anet_userid = '").append(monitor.getNetUserId()).append("' ");
			final List<ParetoData> defectDatas = new LinkedList<ParetoData>();
			startTime = System.currentTimeMillis();
			try {
				jdbcTemplate.query(defcodesSql.toString(),
						new RowCallbackHandler() {
							public void processRow(ResultSet rs) throws SQLException {
								ParetoData bean = new ParetoData();
								bean.setDefectcode(rs.getString("defectcode"));
								bean.setDescription(rs.getString("description"));
								defectDatas.add(bean);
							}
						});
	 
			} catch (Exception e) {
 
				logger.error(e);
			}

			for (int i = 0; i < datas.size(); i++) {
				for (int j = 0; j < defectDatas.size(); j++) {
					if (datas.get(i).getDefectcode().equalsIgnoreCase( defectDatas.get(j).getDefectcode())) {
						datas.get(i).setDescription( defectDatas.get(j).getDescription());
					}
				}
			}
		}
		return datas;
	}

}
